﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using ModelLayer;

namespace DbLayer
{
    public class ProductDb
    {
        private SqlUtility sqlUtility;
        private SqlConnection con;
        public ProductDb()
        {
            sqlUtility = new SqlUtility();
        }
        private int GetMaxId()
        {
            int count = 0;
            using (SqlConnection con = sqlUtility.GetCon())
            {
                con.Open();
                string query = "select max (id) from Product";
                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    count = (int)cmd.ExecuteScalar();
                    cmd.Dispose();
                }
                con.Close();
                con.Dispose();
            }
            return count;
        }
        private Boolean IsEmpty(List<Allergies> _list)
        {
            if (_list == null)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        public int InsertProduct(Product _product)
        {
            int result = 0;

            const string query = "Insert into Product(name, productType, photoId) values(@name, @productType, @photoId)";
            using (SqlConnection con = sqlUtility.GetCon())
            {
                con.Open();
                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    cmd.Parameters.AddWithValue("@name", _product.Name);
                    cmd.Parameters.AddWithValue("@productType", _product.productType);
                    cmd.Parameters.AddWithValue("@photoId", _product.photoId);
                    result += sqlUtility.ExecuteData(cmd, con);
                }//end using

                if (_product.Allergies.Count > 0) //indsæt noget med at 
                {
                    int productId = GetMaxId();
                    con.Open();
                    foreach (Allergies a in _product.Allergies)
                    {
                        const string query2 = "Insert into ProductAllergies(productId, allergiesId) values(@productId, @allergiesId";
                        using (SqlCommand cmd2 = new SqlCommand(query2, con))
                        {
                            cmd2.Parameters.AddWithValue("@productId", productId);
                            cmd2.Parameters.AddWithValue("@allergiesId", a.Id);
                            result += sqlUtility.ExecuteData(cmd2, con);
                        }//end using
                    }//end foreach
                }//end if
                else
                {
                    return result;
                }//end else
            }//end using
            return result;
        }
        private Product BuildProduct(SqlDataReader _rdr)
        {
            Product temProduct = new Product();

            temProduct.Id = sqlUtility.GetSafeInt(_rdr, 0);
            temProduct.Name = sqlUtility.GetSafeString(_rdr, 1);
            temProduct.productType = sqlUtility.GetSafeString(_rdr, 2);
            temProduct.photoId = sqlUtility.GetSafeInt(_rdr, 3);

            if (_rdr.FieldCount > 4)
            {
                temProduct.amount = sqlUtility.getSafeDouble(_rdr, 4);
                temProduct.unit = sqlUtility.GetSafeString(_rdr, 5);
            }
            else
            {
                temProduct.amount = 0;
                temProduct.unit = string.Empty;

            }

            AllergiesDb allergiesDb = new AllergiesDb();
            temProduct.Allergies = allergiesDb.GetAllegiesFromProduct(temProduct.Id);

            return temProduct;

        }
        public List<Product> GetProductByName(string _name)
        {
            List<Product> retlist = new List<Product>();
            using (con = sqlUtility.GetCon())
            {
                con.Open();
                const string query = "select * from Product where name like @name";

                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    cmd.Parameters.AddWithValue("@name", "%" + _name + "%");

                    using (SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        while (rdr.Read())
                        {
                            Product temProduct = new Product();
                            temProduct = BuildProduct(rdr);
                            retlist.Add(temProduct);
                        }
                        rdr.Close();
                        rdr.Dispose();
                    }
                    cmd.Dispose();
                }
                con.Close();
                con.Dispose();
            }
            return retlist;
        }
        public List<Product> GetProductByType(string _type)
        {
            List<Product> retlist = new List<Product>();
            using (con = sqlUtility.GetCon())
            {
                con.Open();
                const string query = "select * from Product where productType=@productType";

                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    cmd.Parameters.AddWithValue("@productType", _type);

                    using (SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        while (rdr.Read())
                        {
                            Product temProduct = new Product();
                            temProduct = BuildProduct(rdr);
                            retlist.Add(temProduct);
                        }
                        rdr.Close();
                        rdr.Dispose();
                    }
                    cmd.Dispose();
                }
                con.Close();
                con.Dispose();
            }
            return retlist;
        }

        public List<Product> GetProductByNameAndType(string _name, string _type)
        {
            List<Product> retlist = new List<Product>();
            using (con = sqlUtility.GetCon())
            {
                con.Open();
                const string query = "select * from Product where name like @name and productType = @type ORDER BY name ASC";

                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    cmd.Parameters.AddWithValue("@name", "%" + _name + "%");
                    cmd.Parameters.AddWithValue("@type", _type);

                    using (SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        while (rdr.Read())
                        {
                            Product temProduct = new Product();
                            temProduct = BuildProduct(rdr);
                            retlist.Add(temProduct);
                        }
                        rdr.Close();
                        rdr.Dispose();
                    }
                    cmd.Dispose();
                }
                con.Close();
                con.Dispose();
            }
            return retlist;

        }
        public Product GetExactProductByName(string _name)
        {
            Product retProduct = null;
            using (con = sqlUtility.GetCon())
            {
                con.Open();
                const string query = "select * from Product where name = @name";

                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    cmd.Parameters.AddWithValue("@name", _name);

                    using (SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        while (rdr.Read())
                        {
                            retProduct = BuildProduct(rdr);
                        }
                        rdr.Close();
                        rdr.Dispose();
                    }
                    cmd.Dispose();
                }
                con.Close();
                con.Dispose();
            }
            return retProduct;
        }
        
        public List<Product>GetRemaningProducts(List<Product> _products)
        {
            string productSelection = "";
            foreach (Product p in _products)
            {
                productSelection += p.Id + ", ";
            }

            if (productSelection.Length > 0)
            {
                productSelection = productSelection.Substring(0, productSelection.Length - 2);

            }

            List<Product> productList = new List<Product>();
            using (con = sqlUtility.GetCon())
            {
                con.Open();
                string select = "Select * from Product";
                string condition = "WHERE NOT Product.id in(" + productSelection + ")";
                string order = "ORDER BY name ASC";
       
                string query = select + " " + condition + " " + order;

                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    using (SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        while (rdr.Read())
                        {
                            Product product = new Product();
                            product = BuildProduct(rdr);
                            productList.Add(product);
                        }
                        rdr.Close();
                        rdr.Dispose();
                    }
                    cmd.Dispose();
                }
                con.Close();
                con.Dispose();
            }
            return productList;
        }
        public List<String> GetProductTypes()
        {
            List<String> retList = new List<String>();

            using (con = sqlUtility.GetCon())
            {
                con.Open();
                const string query = "SELECT DISTINCT productType FROM product ORDER BY productType ASC";

                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    using (SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        while (rdr.Read())
                        {
                            retList.Add(rdr.GetString(0));

                        }
                        rdr.Close();
                        rdr.Dispose();
                    }
                    cmd.Dispose();
                }
                con.Close();
                con.Dispose();
            }
            return retList;
        }


        public List<Product> SelectAllProduct()
        {
            List<Product> retList = new List<Product>();

            using (con = sqlUtility.GetCon())
            {
                con.Open();
                const string query = "Select * from Product ORDER BY Name ASC";

                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    using (SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        while (rdr.Read())
                        {
                            Product temProduct = new Product();
                            temProduct = BuildProduct(rdr);
                            retList.Add(temProduct);
                        }
                        rdr.Close();
                        rdr.Dispose();
                    }
                    cmd.Dispose();
                }
                con.Close();
                con.Dispose();
            }
            return retList;
        }
        public List<Product> GetProductByAllergies(List<Allergies> _list)
        {
            List<Product> retList = new List<Product>();

            foreach (Allergies a in _list)
            {
                using (con = sqlUtility.GetCon())
                {
                    con.Open();
                    const string query = "Select * from ProductAllergies where allergiesId=@allergiesId";
                    using (SqlCommand cmd = new SqlCommand(query, con))
                    {
                        cmd.Parameters.AddWithValue("@allergiesId", a.Id);

                        using (SqlDataReader rdr = cmd.ExecuteReader())
                        {
                            while (rdr.Read())
                            {
                                Product temProduct = new Product();
                                temProduct = BuildProduct(rdr);
                                retList.Add(temProduct);
                            }
                            rdr.Close();
                            rdr.Dispose();
                        }
                        cmd.Dispose();
                    }
                    con.Close();
                    con.Dispose();
                }
            }
            return retList;
        }

        public List<Product> GetProductsByRecipe(Recipe _recipe)
        {
            List<Product> retList = new List<Product>();

            using (con = sqlUtility.GetCon())
            {
                con.Open();
                const string query = "Select Product.id, Product.name, Product.productType, Product.photoId, recipeingredients.amount, recipeingredients.unit from Product join recipeingredients on Product.Id = recipeingredients.productId where recipeingredients.recipeId = @recipeId";
                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    cmd.Parameters.AddWithValue("@recipeId", _recipe.Id);

                    using (SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        while (rdr.Read())
                        {
                            Product temProduct = new Product();
                            temProduct = BuildProduct(rdr);
                            retList.Add(temProduct);
                        }
                        rdr.Close();
                        rdr.Dispose();
                    }
                    cmd.Dispose();
                }
                con.Close();
                con.Dispose();
            }

            return retList;

        }

    }
}

//GetProductByAllergies